# create table mucService
CREATE TABLE mucService (
  serviceID           BIGINT        NOT NULL,
  subdomain           VARCHAR(255)  NOT NULL,
  description         VARCHAR(255),
  PRIMARY KEY (subdomain),
  INDEX mucService_serviceid_idx (serviceID)
);
/
# create table mucServiceProp
CREATE TABLE mucServiceProp (
  serviceID           BIGINT        NOT NULL,
  name                VARCHAR(100)  NOT NULL,
  propValue           TEXT          NOT NULL,
  PRIMARY KEY (serviceID, name)
);
/
# add new indexed column to mucRoom
ALTER TABLE mucRoom ADD COLUMN serviceID BIGINT NOT NULL DEFAULT 1 FIRST;
/
ALTER TABLE mucRoom ADD INDEX mucRoom_serviceid_idx (serviceID);
/
# change mucRooms primary key to be referenced around serviceID
ALTER TABLE mucRoom DROP PRIMARY KEY;
/
ALTER TABLE mucRoom ADD PRIMARY KEY (serviceID,name);
/
# add default entry for conference service and associated jiveID value
INSERT INTO mucService (serviceID, subdomain) VALUES (1, 'conference');
/
INSERT INTO jiveID (idType, id) VALUES (26, 1);
/
# update conference name/desc if theres a custom one set
UPDATE mucService SET mucService.subdomain = ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' )
  WHERE EXISTS ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' );
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.service';
/
UPDATE mucService SET mucService.description = ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' )
  WHERE EXISTS ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' );
/
DELETE FROM jiveProperty WHERE name = 'muc.service-name';
/
# transfer all system properties to muc specific properties
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canOccupantsChangeSubject',propValue FROM jiveProperty WHERE name = 'muc.room.canOccupantsChangeSubject';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.canOccupantsChangeSubject';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.maxUsers',propValue FROM jiveProperty WHERE name = 'muc.room.maxUsers';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.maxUsers';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.publicRoom',propValue FROM jiveProperty WHERE name = 'muc.room.publicRoom';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.publicRoom';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.persistent',propValue FROM jiveProperty WHERE name = 'muc.room.persistent';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.persistent';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.moderated',propValue FROM jiveProperty WHERE name = 'muc.room.moderated';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.moderated';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.membersOnly',propValue FROM jiveProperty WHERE name = 'muc.room.membersOnly';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.membersOnly';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canOccupantsInvite',propValue FROM jiveProperty WHERE name = 'muc.room.canOccupantsInvite';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.canOccupantsInvite';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canAnyoneDiscoverJID',propValue FROM jiveProperty WHERE name = 'muc.room.canAnyoneDiscoverJID';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.canAnyoneDiscoverJID';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.logEnabled',propValue FROM jiveProperty WHERE name = 'muc.room.logEnabled';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.logEnabled';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.loginRestrictedToNickname',propValue FROM jiveProperty WHERE name = 'muc.room.loginRestrictedToNickname';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.loginRestrictedToNickname';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canChangeNickname',propValue FROM jiveProperty WHERE name = 'muc.room.canChangeNickname';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.canChangeNickname';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.registrationEnabled',propValue FROM jiveProperty WHERE name = 'muc.room.registrationEnabled';
/
DELETE FROM jiveProperty WHERE name = 'muc.room.registrationEnabled';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.user.timeout',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.timeout';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.timeout';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.user.idle',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.idle';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.idle';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.log.timeout',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.timeout';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.timeout';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.log.batchsize',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.batchsize';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.batchsize';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'sysadmin.jid',propValue FROM jiveProperty WHERE name = 'xmpp.muc.sysadmin.jid';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.sysadmin.jid';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'discover.locked',propValue FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'create.anyone',propValue FROM jiveProperty WHERE name = 'xmpp.muc.create.anyone';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.create.anyone';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'create.jid',propValue FROM jiveProperty WHERE name = 'xmpp.muc.create.jid';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.create.jid';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'enabled',propValue FROM jiveProperty WHERE name = 'xmpp.muc.enabled';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.enabled';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'unload.empty_days',propValue FROM jiveProperty WHERE name = 'xmpp.muc.unload.empty_days';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.unload.empty_days';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'discover.locked',propValue FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'history.maxNumber',propValue FROM jiveProperty WHERE name = 'xmpp.muc.history.maxNumber';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.history.maxNumber';
/
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'history.type',propValue FROM jiveProperty WHERE name = 'xmpp.muc.history.type';
/
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.history.type';
/

UPDATE jiveVersion set version=17 where name = 'openfire';
/